import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine

# 设置中文字体和图片清晰度
plt.rcParams["font.family"] = ["SimHei", "Heiti TC"]
# plt.rcParams['figure.dpi'] = 300

# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 
postgres_schema = "datawarehouse" 

# 建立数据库连接
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
engine = create_engine(conn_string)
conn = engine.connect()

# 从数据库查询数据的函数
def query_data_from_database():
    """从数据库查询三个表的数据"""
    # 客户表查询
    query_customer = f"""
    SELECT * FROM {postgres_schema}.dim_customer;
    """
    dim_customer = pd.read_sql(query_customer, conn)
    
    # 产品表查询
    query_product = f"""
    SELECT * FROM {postgres_schema}.dim_product;
    """
    dim_product = pd.read_sql(query_product, conn)
    
    # 销售表查询
    query_sales = f"""
    SELECT * FROM {postgres_schema}.fact_sales;
    """
    fact_sales = pd.read_sql(query_sales, conn)
    
    return dim_customer, dim_product, fact_sales

# 1. 客户性别分布饼图
def plot_customer_gender_distribution(dim_customer):
    gender_counts = dim_customer['cust_gender'].value_counts(dropna=False)
    plt.figure()
    plt.pie(gender_counts, labels=gender_counts.index, autopct='%1.1f%%', startangle=90)
    plt.title('客户性别分布')
    plt.show()

# 2. 每月新增客户趋势图
def plot_new_customers_by_month(dim_customer):
    if 'cust_create_date' in dim_customer.columns and not dim_customer['cust_create_date'].empty:
        dim_customer['month'] = pd.to_datetime(dim_customer['cust_create_date']).dt.to_period('M')
        monthly_customers = dim_customer['month'].value_counts().sort_index().reset_index()
        monthly_customers.columns = ['month', 'count']
        monthly_customers['month'] = monthly_customers['month'].astype(str)
        
        plt.figure()
        sns.lineplot(x='month', y='count', data=monthly_customers, marker='o')
        plt.title('每月新增客户数量')
        plt.xlabel('月份')
        plt.ylabel('客户数量')
        # plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()
    else:
        print("客户创建日期数据不存在或为空，无法绘制每月新增客户趋势图")

# 3. 产品类别与平均成本关系图
def plot_product_category_cost(dim_product):
    if 'prd_category' in dim_product.columns and 'prd_cost' in dim_product.columns:
        category_cost = dim_product.groupby('prd_category')['prd_cost'].mean().reset_index()
        
        plt.figure()
        ax = sns.barplot(x='prd_category', y='prd_cost', data=category_cost)
        for i, v in enumerate(category_cost['prd_cost']):
            plt.text(i, v + 30, f'{v:.0f}', ha='center')
        plt.title('产品类别与平均成本')
        plt.xlabel('产品类别')
        plt.ylabel('平均成本')
        plt.show()
    else:
        print("产品类别或成本数据不存在，无法绘制产品类别与平均成本图")

# 4. 产品类别销售分布饼图
def plot_category_sales_distribution(category_revenue):
    plt.figure()
    plt.pie(
        category_revenue['revenue'], 
        labels=category_revenue['prd_category'], 
        autopct='%1.1f%%', 
        startangle=90,
        textprops={'fontsize': 10}
    )
    plt.title('产品类别销售分布')
    plt.axis('equal')  # 使饼图为正圆形
    plt.tight_layout()
    plt.show()
    
# 5. 每日销售趋势图
def plot_daily_sales_trend(daily_revenue):
    """绘制每日销售趋势图"""
    if 'sls_order_date' in daily_revenue.columns and 'revenue' in daily_revenue.columns:
        try:
            # 确保日期列是datetime类型
            if not pd.api.types.is_datetime64_any_dtype(daily_revenue['sls_order_date']):
                daily_revenue['sls_order_date'] = pd.to_datetime(daily_revenue['sls_order_date'])
            
            # 按日期排序
            daily_revenue = daily_revenue.sort_values('sls_order_date')
            
            plt.figure()
            ax = sns.lineplot(x='sls_order_date', y='revenue', data=daily_revenue, marker='o')
            
            # 添加数据标签
            # for x, y in zip(daily_revenue['sls_order_date'], daily_revenue['revenue']):
            #     ax.annotate(f'{y:.0f}', 
            #                 (x, y), 
            #                 textcoords='offset points', 
            #                 xytext=(0, 10), 
            #                 ha='center',
            #                 rotation=45)
            
            plt.title('每日销售趋势')
            plt.xlabel('订单日期')
            plt.ylabel('销售额')
            plt.xticks(rotation=45)
            plt.grid(True, linestyle='--', alpha=0.7)
            plt.tight_layout()
            plt.show()
            
        except Exception as e:
            print(f"绘制每日销售趋势图时出错: {str(e)}")
    else:
        print("error")

# 6. 客户国家分布热力图
def plot_customer_country_heatmap(fact_sales, dim_product, dim_customer):
    """绘制客户国家与产品类别分布热力图"""
    try:
        # 检查必要的列是否存在
        required_columns = {
            'fact_sales': ['prd_key', 'cust_num'],
            'dim_product': ['prd_key', 'prd_category'],
            'dim_customer': ['cust_num', 'cust_country']
        }
        
        # 验证每个表的列
        for df, name in zip([fact_sales, dim_product, dim_customer], 
                            ['fact_sales', 'dim_product', 'dim_customer']):
            for col in required_columns[name]:
                if col not in df.columns:
                    print(f"{name}表缺少必要的列: {col}，无法绘制热力图")
                    return
        
        # 正确合并三个表
        # 先合并销售表和产品表，获取prd_category
        sales_product = pd.merge(
            fact_sales, 
            dim_product[['prd_key', 'prd_category']],  # 只选择需要的列
            on='prd_key', 
            how='left'
        )
        
        # 再合并客户表，获取cust_country
        customer_product = pd.merge(
            sales_product, 
            dim_customer[['cust_num', 'cust_country']],  # 只选择需要的列
            on='cust_num', 
            how='left'
        )
        
        # 检查合并后的数据是否有内容
        if customer_product.empty:
            print("合并后的数据为空，无法绘制热力图")
            return
            
        # 创建国家和产品类别的交叉表
        country_category = pd.crosstab(
            customer_product['cust_country'], 
            customer_product['prd_category'],
            values=customer_product['sls_quantity'],
            aggfunc='sum',
            dropna=False
        ).fillna(0)
        
        # 检查交叉表是否有内容
        if country_category.empty or (country_category.sum().sum() == 0):
            print("没有足够的数据来创建有意义的热力图")
            return
            
        # 绘制热力图
        plt.figure()
        ax = sns.heatmap(country_category, annot=True, fmt='.0f', cmap='YlGnBu', 
                         linewidths=.5, cbar_kws={'label': '销售数量'})
        
        plt.title('客户国家与产品类别销售分布热力图')
        plt.xlabel('产品类别')
        plt.ylabel('客户国家')
        plt.tight_layout()
        plt.show()
        
    except Exception as e:
        print(f"绘制热力图时出错: {str(e)}")


def main():
    """主函数：执行数据查询和所有可视化"""
    try:
        # 从数据库查询数据
        dim_customer, dim_product, fact_sales = query_data_from_database()
        
        if dim_customer.empty or dim_product.empty or fact_sales.empty:
            print("数据加载失败，无法继续执行可视化")
            return
        
        # 执行各项可视化
        plot_customer_gender_distribution(dim_customer)
        plot_new_customers_by_month(dim_customer)
        plot_product_category_cost(dim_product)
        
        # 合并数据并进行销售分析
        sales_products, category_revenue, daily_revenue = merge_and_analyze_data(fact_sales, dim_product)
        
        plot_category_sales_distribution(category_revenue)
        plot_daily_sales_trend(daily_revenue)
        plot_customer_country_heatmap(fact_sales, dim_product, dim_customer)
        
        print("可视化完成")
        
    except Exception as e:
        print(f"执行主程序时出错: {str(e)}")
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()
        if engine:
            engine.dispose()
        print("已关闭数据库连接")

# 修改merge_and_analyze_data函数，接收参数
def merge_and_analyze_data(fact_sales, dim_product):
    """合并销售表和产品表进行销售分析"""
    # 合并销售表和产品表
    sales_products = pd.merge(fact_sales, dim_product, left_on='prd_key', right_on='prd_key', how='left')
    
    # 计算销售额
    sales_products['revenue'] = sales_products['sls_quantity'] * sales_products['sls_price']
    
    # 按产品类别统计销售额
    category_revenue = sales_products.groupby('prd_category')['revenue'].sum().reset_index()
    
    # 按订单日期统计每日销售额
    daily_revenue = sales_products.groupby('sls_order_date')['revenue'].sum().reset_index()
    
    return sales_products, category_revenue, daily_revenue


# 修改主程序入口
if __name__ == "__main__":
    main()